#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/3/31 14:16
# @Author : 闫峰
# @File    : db_pool.py

import pymysql

from conf.settings import ensure_path_sep
from utils.readFileUtils.get_all_files_path import get_all_files
from utils.readFileUtils.yaml_control import GetYamlData
from dbutils.pooled_db import PooledDB
from typing import Optional, Tuple, List, Dict


class Database:
    def __init__(self, database_key):
        # 因为目前系统逻辑库有不同的ip地址,因此需要兼容动态连接多个数据库源

        yaml_path, database_key, db_name = database_key.split(":")
        yaml_path = ensure_path_sep(f"\\conf\\{yaml_path}")

        self.mysql = GetYamlData(yaml_path).get_yaml_data()[database_key]
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=5,
            host=self.mysql["host"],
            user=self.mysql["user"],
            port=self.mysql["port"],
            password=self.mysql["password"],
            database=db_name,
            charset="utf8mb4"
        )

    def execute(self, sql: str, value: Optional[Tuple] = None) -> int:
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        if value is not None and not isinstance(value, tuple):
            raise ValueError("The value parameter must be a tuple or None.")

        conn = self.pool.connection()
        try:
            with conn.cursor() as cursor:
                if value is None:
                    rows = cursor.execute(sql)
                else:
                    rows = cursor.execute(sql, value)
                conn.commit()
                return rows
        except Exception as e:
            conn.rollback()
            # raise e
        finally:
            conn.close()

    def fetch_all(self, sql: str, value: Optional[Tuple] = None) -> List[Dict]:
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        if value is not None and not isinstance(value, tuple):
            raise ValueError("The value parameter must be a tuple or None.")

        conn = self.pool.connection()
        try:
            with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
                if value is None:
                    cursor.execute(sql)
                else:
                    cursor.execute(sql, value)
                result = cursor.fetchall()
                return result
        except Exception as e:
            # raise e
            pass
        finally:
            conn.close()


if __name__ == '__main__':
    # ceshi_log = Database(r"D:\zhcx-service-test\config\ceshi_mysql_config.yaml", "log_db")
    # sql3 = """
    # INSERT INTO `znkdceshilog`.sys_marketlisting_count
    # (OrderSourceType, OrderSourceId, OrderSourceCountry, LogisticType, UserId, Quota, ApiNum, PublishNum, CreatorId, Creator, CreationTime, LastModifierId, LastModifier, LastModificationTime, order_source_name)
    # VALUES
    # (97, %s, %s, '', '', '', %s, %s, 1, 'Admin', NOW(), 2, 'Manager', NOW(), %s);
    #             """
    # res = ceshi_log.execute(sql3, (12312, "CN", 123, 333, "asdasdasd"))
    # print(res)
    straa = "cgboss_beta08.yaml:test_union"
    db = Database(straa)
    sql = """
    select 
       JSON_EXTRACT(type_custom, '$.instanceId')    AS instanceId
        from d_cgboss_info.task_sitem_101
        where pitem_id = 3756
        and status = 252
    """
    res = db.fetch_all(sql, ())
    print(res)
